﻿using System;
using System.Collections.Generic;
using System.Text;
using ERP.DAL.Base;

namespace ERP.DataAccess.Factory
{
    public class SQLServer_StoreFactory:IStoreFactory
    {
        List<String> lstColumn;
        Dictionary<String, KeyValuePair<String, int>> dicColumn_Types;
        String strPrimaryKey;
        KeyValuePair<String, int> pairPrimaryKeyType;
        IDBUtil DBUtil;
        public void InitData(IDBUtil _DBUtil, String strTable)
        {
            DBUtil = _DBUtil;
            strPrimaryKey = DBUtil.GetPrimaryKeyOnTable(strTable);
            pairPrimaryKeyType = DBUtil.GetTypeOfColumnOnTable(strTable, strPrimaryKey);
            lstColumn = DBUtil.GetListColumnNameOfTable(strTable);
            dicColumn_Types = new Dictionary<String, KeyValuePair<String, int>>();
            foreach (String strColumn in lstColumn)
            {
                KeyValuePair<String, int> pairColumnType = DBUtil.GetTypeOfColumnOnTable(strTable, strColumn);
                dicColumn_Types.Add(strColumn, pairColumnType);
            }
        }
        public String GenStore_Insert(String strTable)
        {
            StringBuilder strBuild = new StringBuilder();
            strBuild.Append("ALTER PROCEDURE " + strTable + "_Insert\n");
            foreach (KeyValuePair<String, KeyValuePair<String, int>> pair in dicColumn_Types)
            {
                if (pair.Value.Value == 0)
                    strBuild.Append(String.Format("\t@{0} {1},\n", pair.Key, pair.Value.Key));
                else
                    strBuild.Append(String.Format("\t@{0} {1}({2}),\n", pair.Key, pair.Value.Key, pair.Value.Value));
                
            }
            strBuild.Remove(strBuild.Length - 2, 1);
            strBuild.Append("\n");
            strBuild.Append("AS\n");
            strBuild.Append("BEGIN\n");
            strBuild.Append("SET NOCOUNT ON\n");
            strBuild.Append("INSERT INTO " + strTable + "\n(\n");
            for (int i = 0; i < lstColumn.Count - 1; i++)
            {
                strBuild.Append("\t[" + lstColumn[i] + "],\n");
            }
            strBuild.Append("\t[" + lstColumn[lstColumn.Count - 1] + "]\n)\n");
            strBuild.Append("VALUES\n(\n");
            for (int i = 0; i < lstColumn.Count - 1; i++)
            {
                strBuild.Append("\t@" + lstColumn[i] + ",\n");
            }
            strBuild.Append("\t@" + lstColumn[lstColumn.Count - 1] + "\n)\n");

            strBuild.Append("END\n");
            return strBuild.ToString();
        }
        public String GenStore_Update(String strTable)
        {
            StringBuilder strBuild = new StringBuilder();
            strBuild.Append("ALTER PROCEDURE " + strTable + "_Update\n");
            foreach (KeyValuePair<String, KeyValuePair<String, int>> pair in dicColumn_Types)
            {
                if (pair.Value.Value == 0)
                    strBuild.Append(String.Format("\t@{0} {1},\n", pair.Key, pair.Value.Key));
                else
                    strBuild.Append(String.Format("\t@{0} {1}({2}),\n", pair.Key, pair.Value.Key, pair.Value.Value));
                
            }
            strBuild.Remove(strBuild.Length - 2, 1);
            strBuild.Append("\n");
            strBuild.Append("AS\n");
            strBuild.Append("BEGIN\n");
            strBuild.Append("SET NOCOUNT ON\n");
            strBuild.Append("UPDATE " + strTable + " SET\n");
            for (int i = 0; i < lstColumn.Count - 1; i++)
            {
                strBuild.Append(String.Format("\t[{0}] = @{0},\n", lstColumn[i]));
            }
            strBuild.Append(String.Format("\t[{0}] = @{0}\n", lstColumn[lstColumn.Count - 1]));
            strBuild.Append("WHERE\n");
            strBuild.Append(String.Format("\t[{0}] = @{0}\n", strPrimaryKey));
            strBuild.Append("END\n");
            return strBuild.ToString();
        }
        public String GenStore_SelectById(String strTable)
        {
            StringBuilder strBuild = new StringBuilder();
            strBuild.Append("ALTER PROCEDURE " + strTable + "_Select\n");
            if (pairPrimaryKeyType.Value == 0)
                strBuild.Append(String.Format("\t@{0} {1}\n", strPrimaryKey, pairPrimaryKeyType.Key));
            else
                strBuild.Append(String.Format("\t@{0} {1}({2})\n", strPrimaryKey, pairPrimaryKeyType.Value));
            
            strBuild.Append("\n");
            strBuild.Append("AS\n");
            strBuild.Append("BEGIN\n");
            strBuild.Append("SET NOCOUNT ON\n");
            strBuild.Append("SELECT * FROM " + strTable + "\nWHERE\n");
            strBuild.Append(String.Format("\t[{0}] = @{0}\n", strPrimaryKey));
           
            if (DBUtil.CheckColumnIsExist(strTable, "AAStatus") == true)
            {
                strBuild.Append("AND\n");
                strBuild.Append("\t[AAStatus] = 'Alive'\n");
            }

            strBuild.Append("END\n");
            return strBuild.ToString();
        }
        public String GenStore_SelectAll(String strTable)
        {
            StringBuilder strBuild = new StringBuilder();
            strBuild.Append("ALTER PROCEDURE " + strTable + "_SelectAll\n");
            strBuild.Append("\n");
            strBuild.Append("AS\n");
            strBuild.Append("BEGIN\n");
            strBuild.Append("SET NOCOUNT ON\n");
            strBuild.Append("SELECT * FROM \n" + strTable);
            if (DBUtil.CheckColumnIsExist(strTable, "AAStatus") == true)
            {
                strBuild.Append("\nWHERE\n");
                strBuild.Append("\t[AAStatus] = 'Alive'\n");
            }
            strBuild.Append("\nEND\n");
            return strBuild.ToString();
        }

        public String GenStore_Delete(String strTable)
        {
            StringBuilder strBuild = new StringBuilder();
            strBuild.Append("ALTER PROCEDURE " + strTable + "_DeleteByID\n");
            if (pairPrimaryKeyType.Value == 0)
                strBuild.Append(String.Format("\t@{0} {1}\n", strPrimaryKey, pairPrimaryKeyType.Key));
            else
                strBuild.Append(String.Format("\t@{0} {1}({2})\n", strPrimaryKey, pairPrimaryKeyType.Key, pairPrimaryKeyType.Value));
            
            strBuild.Append("\n");
            strBuild.Append("AS\n");
            strBuild.Append("BEGIN\n");
            strBuild.Append("SET NOCOUNT ON\n");
            if (DBUtil.CheckColumnIsExist(strTable, "AAStatus") == true)
            {
                strBuild.Append("UPDATE " + strTable + "\n");
                strBuild.Append("SET [AAStatus] = 'Delete'\n");
            }
            else
            {
                strBuild.Append("DELETE " + strTable + "\n");
            }
            
            strBuild.Append("WHERE\n");
            strBuild.Append(String.Format("\t[{0}] = @{0}\n", strPrimaryKey));
            strBuild.Append("END\n");
            return strBuild.ToString();
        }
        public String GenStore_DeleteAll(String strTable)
        {
            StringBuilder strBuild = new StringBuilder();
            strBuild.Append("ALTER PROCEDURE " + strTable + "_DeleteAll\n");
            strBuild.Append("\n");
            strBuild.Append("AS\n");
            strBuild.Append("BEGIN\n");
            strBuild.Append("SET NOCOUNT ON\n");
            if (DBUtil.CheckColumnIsExist(strTable, "AAStatus") == true)
            {
                strBuild.Append("UPDATE " + strTable + "\n");
                strBuild.Append("SET [AAStatus] = 'Delete'\n");
            }
            else
            {
                strBuild.Append("DELETE " + strTable + "\n");
            }
            
            strBuild.Append("END\n");
            return strBuild.ToString();
        }
    }
}
